#!/home/berald01/.local/bin/python

import sys
import argparse
import subprocess
import os
import shutil

parser = argparse.ArgumentParser(description= """
DESCRIPTION
    Upload to postgres sblab a bismark report.
    Only rows not already in the target table (bismark_mapping_report) are imported.
    
    The report is parsed by  bismark_mapping_report2tab.py before uploading.
    
    NB: upload_bismark_mapping_report.py can be executed on servers not directly connected
    to sblab database since the report is scp'd to the machine hosting sblab along with
    the sql script to upload it. (However, it is necessary to be able to ssh/scp to
    the machine hosting sblab).

EXAMPLE
    upload_bismark_mapping_report.py to010_bs.r_1.fq.gz_bt2_Bismark_mapping_report.txt

SEE ALSO:
    http://code.google.com/p/bioinformatics-misc/source/browse/trunk/bismark_mapping_report2tab.py

TODO:
    This script is very clunky.
    - USe psycopg2 instead of ssh/scp
""", formatter_class= argparse.RawTextHelpFormatter)

parser.add_argument('infile',
                   help='''Report file to upload.
                   ''')
parser.add_argument('-v', '--verbose',
                   action= 'store_true',
                   help='''Print a little more verbose output of what is happening.
                   ''')
args = parser.parse_args()

# -----------------------------------------------------------------------------

## This part converts the report to tabular ##
bmr_tabular= args.infile + '.bmr'
cmd= 'bismark_mapping_report2tab.py %s > %s' %(args.infile, bmr_tabular)
p= subprocess.Popen(cmd, shell= True, stderr= subprocess.PIPE)
p.wait()
reporterr= p.stderr.read()
if not reporterr == '':
    sys.exit(reporterr)
## ----------------------------------------------------------------------------

bmr= open(bmr_tabular).readlines()
newname= bmr_tabular + '.tmp'

if bmr[0].startswith('bismark_report_for\tfull_path'):
    ## Determine whether the report has header
    ## This part is obsolete, but keep it.
    fout= open(newname, 'w')
    for line in bmr[1:]:
        fout.write(line)
    fout.close()
else:
    shutil.copy(bmr_tabular, newname)

reportname= os.path.split(newname)[1]

## SQL to upload the report
sql= """-- Script to upload bismark_report: 
CREATE TEMP TABLE tmp_bismark_mapping_report (LIKE bismark_mapping_report);
COPY tmp_bismark_mapping_report FROM '%s' WITH CSV DELIMITER E'\\t' NULL AS 'NA';
INSERT INTO bismark_mapping_report (SELECT * FROM tmp_bismark_mapping_report EXCEPT SELECT * FROM bismark_mapping_report);
DROP TABLE tmp_bismark_mapping_report; """ %("/tmp/" + reportname)

sqlscript= bmr_tabular + '.upload.tmp.sql'
fout= open(sqlscript, 'w')
fout.write(sql)
fout.close()

if args.verbose:
    print(sql + '\n')

## scp report to /tmp
cmd= 'scp %s $mac_office:/tmp; scp %s $mac_office:/tmp' %(newname, sqlscript)
if args.verbose:
    print(cmd)
p= subprocess.Popen(cmd, shell= True)
p.wait()
cmd= '''ssh $mac_office 'source ~/.bash_profile;
    chmod 777 /tmp/%(report)s;
    psql -U dberaldi -d sblab -w < /tmp/%(sqlscript)s;
    rm /tmp/%(report)s;
    rm /tmp/%(sqlscript)s;
    exit' ''' %{'report': reportname, 'sqlscript': sqlscript}
if args.verbose:
    print(cmd)
p= subprocess.Popen(cmd, shell= True)
p.wait()
os.remove(newname)
os.remove(sqlscript)
os.remove(bmr_tabular)
sys.exit()
